package com.todd.studentms.util;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;

public class Tdber {

    private static String url;
    private static String driver;
    private static String userName;
    private static String password;

    // 加载配置参数
    static {
        Properties pp = null;
        InputStream in = null;
        try {
            pp = new Properties();
            in = Tdber.class.getClassLoader().getResourceAsStream("db.properties");
            pp.load(in);
            // 装载
            Tdber.url = pp.getProperty("url");
            Tdber.driver = pp.getProperty("driver");
            Tdber.userName = pp.getProperty("username");
            Tdber.password = pp.getProperty("password");
            in.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 查
     *
     * @param obj : new Person().setXXX()
     * @return 结果
     */
    public static <T> List<T> query(T obj) {
        try {
            Map<String, Object> readyInfo = generateSelectSQLAndParams(obj);
            String sql = (String) readyInfo.get("sql");
            Object[] params = (Object[]) readyInfo.get("params");

            // 输出生成的sql和对应位置的参数
            showSQLAndParams(readyInfo);

            return listMapToEntity(runQuery(sql, params), obj);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public static <T> T queryOne(T obj) {
        return query(obj).get(0);
    }

    public static <T> List<T> queryLike(T obj) {
        try {
            Map<String, Object> readyInfo = generateSelectLikeSQLAndParams(obj);
            String sql = (String) readyInfo.get("sql");
            Object[] params = (Object[]) readyInfo.get("params");

            // 输出生成的sql和对应位置的参数
            showSQLAndParams(readyInfo);

            return listMapToEntity(runQuery(sql, params), obj);
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }


    /**
     * 改
     *
     * @param obj: new Person().setXXX()
     * @return true or false
     */
    public static boolean update(Object obj) {
        Map<String, Object> readyInfo = generateUpdateSQLAndParams(obj);
        String sql = (String) readyInfo.get("sql");
        Object[] params = (Object[]) readyInfo.get("params");

        // 输出生成的sql和对应位置的参数
        showSQLAndParams(readyInfo);

        return runExecute(sql, params);
    }

    /**
     * 增
     *
     * @param obj: new Person().setXXX()
     * @return true or false
     */
    public static boolean insert(Object obj) {
        Map<String, Object> readyInfo = generateInsertSQLAndParams(obj);
        String sql = (String) readyInfo.get("sql");
        Object[] params = (Object[]) readyInfo.get("params");

        // 输出生成的sql和对应位置的参数
        showSQLAndParams(readyInfo);

        return runExecute(sql, params);
    }

    /**
     * 删
     *
     * @return
     */
    public static boolean delete(Class cls, Object[] ids) {
        Map<String, Object> readyInfo = generateDeleteSQLAndParams(cls, ids);
        String sql = (String) readyInfo.get("sql");
        Object[] params = (Object[]) readyInfo.get("params");

        // 输出生成的sql和对应位置的参数
        showSQLAndParams(readyInfo);

        return runExecute(sql, params);
    }

    public static boolean delete(Class cls, Object id) {
        return delete(cls, new Object[]{id});
    }

    ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    /////////////////////////////////////////////     内部方法     /////////////////////////////////////////////////////
    ///////////////////////////////////////////////////////////////////////////////////////////////////////////////////

    //获取conn
    private static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url, userName, password);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return conn;
    }

    // 关闭conn
    private static void close(ResultSet rs, PreparedStatement ps, Connection conn) {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    //查询
    public static List<Map<String, Object>> runQuery(String querySql, Object[] pars) {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement(querySql);
            if (pars != null)
                for (int i = 0; i < pars.length; i++) {
                    ps.setObject(i + 1, pars[i]);
                }
            ResultSet rs = ps.executeQuery();
            List<Map<String, Object>> objMapList = new LinkedList<>();
            while (rs.next()) {
                Map<String, Object> objMap = new HashMap<String, Object>();
                ResultSetMetaData rsm = rs.getMetaData();
                int colCount = rsm.getColumnCount();
                for (int i = 0; i < colCount; i++) {
                    String colName = rsm.getColumnLabel(i + 1); // 为什么是getColumnLabel而不是getColumnName?
                    Object colValue = rs.getObject(colName);
                    objMap.put(colName, colValue);
                }
                objMapList.add(objMap);
            }
            return objMapList;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(null, ps, conn);
        }

        return null;
    }

    //增删改
    public static boolean runExecute(String sqlTemplate, Object[] pars) throws RuntimeException {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            conn.setAutoCommit(false);
            ps = conn.prepareStatement(sqlTemplate);
            if (pars != null)
                for (int i = 0; i < pars.length; i++) {
                    ps.setObject(i + 1, pars[i]);
                }
            boolean flag = ps.executeUpdate() > 0;
            conn.commit();
            return flag;
        } catch (SQLException e) {
            e.printStackTrace();
            if (conn != null) {
                try {
                    conn.rollback();
                    throw new RuntimeException();
                } catch (SQLException e1) {
                    e1.printStackTrace();
                }
            }
            return false;
        } finally {
            close(null, ps, conn);
        }
    }

    //返回单个值
    public static String runOneValue(String sql, Object[] pars) {
        Connection conn = null;
        PreparedStatement ps = null;
        String value = null;
        try {
            conn = getConnection();
            ps = conn.prepareStatement(sql);
            if (pars != null)
                for (int i = 0; i < pars.length; i++) {
                    ps.setObject(i + 1, pars[i]);
                }
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                value = rs.getString(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(null, ps, conn);
        }
        return value;
    }

    // 驼峰转下划线
    private static String humpToLine(String str) {
        StringBuffer s = new StringBuffer("");
        for (char ch : str.toCharArray()) {
            if (ch >= 'A' && ch <= 'Z') {
                s.append("_");
                s.append((char) (ch + 32));
            } else {
                s.append(ch);
            }
        }
        String news = new String(s);
        if (news.charAt(0) == '_') {
            news = news.replaceFirst("_", "");
        }
        return news;
    }

    // 下划线转驼峰
    private static String lineToHump(String str) {
        StringBuffer s = new StringBuffer("");
        char[] chars = str.toCharArray();
        for (int i = 0; i < chars.length; i++) {
            if (chars[i] == '_') {
                s.append((char) (chars[++i] - 32));
            } else {
                s.append(chars[i]);
            }
        }
        return new String(s);
    }

    // 输出生成好的sql语句和参数
    private static void showSQLAndParams(Map<String, Object> map) {
        // 输出生成的sql和对应位置的参数
        System.out.println("SQL    >>>  " + map.get("sql"));
        System.out.print("Params >>>  ");
        Arrays.stream((Object[]) map.get("params")).forEach(item -> {
            System.out.print(item);
            System.out.print(", ");
        });
        System.out.println();
    }

    // 将List<Map> 包装成List<T>
    public static <T> List<T> listMapToEntity(List<Map<String, Object>> maps, T obj) throws Exception {
        List<T> result = new ArrayList();
        Class cls = obj.getClass();
        for (Map<String, Object> map : maps) {
            T item = (T) cls.newInstance();
            for (String key : map.keySet()) {
                Field field = null;
                try {
                    field = cls.getDeclaredField(lineToHump(key));
                } catch (NoSuchFieldException e) {
                    continue;
                }
                field.setAccessible(true);
                field.set(item, map.get(key));
            }
            result.add(item);
        }
        return result;
    }

    // 生成select语句和参数
    private static Map<String, Object> generateSelectSQLAndParams(Object obj) {
        Class cls = obj.getClass();
        String tableName = humpToLine(cls.getSimpleName());
        StringBuffer sql = new StringBuffer("select * from `" + tableName + "` where 1=1 ");
        Map<String, Object> map = new HashMap<>();
        List<Object> params = new ArrayList<>();
        for (Field field : cls.getDeclaredFields()) {
            try {
                field.setAccessible(true);
                Object fieldValue = field.get(obj);
                // 没有nonparticipation注解且值不空, 就参与拼接
                if (!field.isAnnotationPresent(NonParticipation.class) && null != fieldValue) {
                    sql.append("and " + humpToLine(field.getName()) + " = ? ");
                    params.add(fieldValue);
                }
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
        map.put("sql", new String(sql));
        map.put("params", params.toArray());
        return map;
    }

    // 生成update语句和参数
    private static Map<String, Object> generateUpdateSQLAndParams(Object obj) {
        Class cls = obj.getClass();
        String tableName = humpToLine(cls.getSimpleName());
        Map<String, Object> map = new HashMap<>();
        List<Object> params = new ArrayList<>();
        boolean isFirst = true;
        String idFieldName = "id";
        Object idFieldvalue = null;
        StringBuffer sql = new StringBuffer("update `" + tableName + "` set ");
        // 生成set ? = ?...
        for (Field field : cls.getDeclaredFields()) {
            try {
                field.setAccessible(true);
                Object fieldValue = field.get(obj);
                // 记录id属性的名字和值
                if (field.isAnnotationPresent(ID.class)) {
                    idFieldName = humpToLine(field.getName());
                    idFieldvalue = field.get(obj);
                } else
                    // 没有nonparticipation, 值不为空 ,且不是主键, 就参与update的set拼接
                    if (field.getAnnotation(NonParticipation.class) == null && null != fieldValue && !field.isAnnotationPresent(ID.class)) {
                        // 如果是第一次 xxx = xxx, 前面没有逗号
                        if (isFirst) {
                            sql.append(humpToLine(field.getName()) + " = ? ");
                            isFirst = false;
                        } else {
                            sql.append(", " + humpToLine(field.getName()) + " = ? ");
                        }
                        params.add(fieldValue);
                    }
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
        // where按id修改
        sql.append("where " + idFieldName + "= ? ");
        params.add(idFieldvalue);
        map.put("sql", new String(sql));
        map.put("params", params.toArray());
        return map;
    }

    // 生成insert语句和参数
    private static Map<String, Object> generateInsertSQLAndParams(Object obj) {
        Class cls = obj.getClass();
        String tableName = humpToLine(cls.getSimpleName());
        StringBuffer sql = new StringBuffer("insert into `" + tableName + "`(");
        Map<String, Object> map = new HashMap<>();
        List<Object> params = new ArrayList<>();
        boolean isFirst = true; // 是否是循环的第一次
        String fieldName = null; // 实体类的属性名
        int count = 0; // 有几个列要插入?
        // 遍历Fields生成要指定那些列
        for (Field field : cls.getDeclaredFields()) {
            try {
                field.setAccessible(true);
                Object fieldValue = field.get(obj);
                // 没有nonparticipation注解且值不空, 就参与拼接
                if (!field.isAnnotationPresent(NonParticipation.class) && null != fieldValue) {
                    fieldName = humpToLine(field.getName());
                    if (isFirst) {
                        sql.append(fieldName + " ");
                        isFirst = false;
                    } else {
                        sql.append(", " + fieldName + " ");
                    }
                    params.add(fieldValue);
                    count++;
                }
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
        sql.append(")");
        // 开始生成values中的问号
        sql.append(" values(");
        isFirst = true;
        for (int i = 0; i < count; i++) {
            if (isFirst) {
                sql.append("? ");
                isFirst = false;
            } else {
                sql.append(",? ");
            }
        }
        sql.append(")");
        map.put("sql", new String(sql));
        map.put("params", params.toArray());
        return map;
    }

    // 生成delete语句和参数(只能通过id删除)
    private static Map<String, Object> generateDeleteSQLAndParams(Class cls, Object[] ids) {
        String tableName = humpToLine(cls.getSimpleName());
        StringBuffer sql = new StringBuffer("delete from `" + tableName + "` where ");
        Map<String, Object> map = new HashMap<>();
        Object idFieldName = null;
        // 拿到id列的名字
        for (Field field : cls.getDeclaredFields()) {
            try {
                field.setAccessible(true);
                if (!field.isAnnotationPresent(NonParticipation.class) && field.isAnnotationPresent(ID.class)) {
                    idFieldName = humpToLine(field.getName());
                    break;
                }
            } catch (Exception e) {
                e.printStackTrace();
            }
        }
        // 开始拼接in
        sql.append(idFieldName + " in (");
        boolean isFirst = true;
        for (Object id : ids) {
            if (isFirst) {
                sql.append("? ");
                isFirst = false;
            } else {
                sql.append(",? ");
            }
        }
        sql.append(")");
        map.put("sql", new String(sql));
        map.put("params", ids);
        return map;
    }

    // 生成模糊查询select语句
    private static Map<String,Object> generateSelectLikeSQLAndParams(Object obj){
        Class cls = obj.getClass();
        String tableName = humpToLine(cls.getSimpleName());
        StringBuffer sql = new StringBuffer("select * from `" + tableName + "` where 1=1 ");
        Map<String, Object> map = new HashMap<>();
        List<Object> params = new ArrayList<>();
        for (Field field : cls.getDeclaredFields()) {
            try {
                field.setAccessible(true);
                Object fieldValue = field.get(obj);
                // 没有nonparticipation注解且值不空, 就参与拼接
                if (!field.isAnnotationPresent(NonParticipation.class) && null != fieldValue) {
                    sql.append("and " + humpToLine(field.getName()) + " like ? ");
                    params.add("%"+fieldValue+"%");
                }
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
        }
        map.put("sql", new String(sql));
        map.put("params", params.toArray());
        return map;
    }
}